﻿-- =============================================
-- Author:		Dmitry NAzarov
-- Create date: 29.03.2011
-- Description:	Отчет Оплаченные изделия подотчет Даты платежей
-- =============================================
CREATE function [dbo].[f_subreport_paid_products] (
	@p_contract_id	int,
	@p_curreny_year bit,
	@Currency_ID int,
	@CS_End_Date date,
	@p_Prod_Unit_Name nvarchar(50))
	RETURNS nvarchar(500)
AS
BEGIN
	declare @m_return nvarchar(500)
	declare @tmpDate date
	declare @cursor cursor

	--set @m_return = STR(@p_contract_id) + CONVERT(nvarchar(50),@p_curreny_year, 104) + CONVERT(nvarchar(50),@Currency_ID, 104) + CONVERT(nvarchar(50),@CS_End_Date, 104) + @p_Prod_Unit_Name

    SET @cursor = CURSOR SCROLL FOR 
	SELECT     pi.P_DATE
	FROM         VIEW_SEARCH_P_U_FOR_FORMS AS pu INNER JOIN
	                      CP_STAGE AS cs ON cs.ID = pu.CP_STAGE_ID INNER JOIN
	                      PID ON PID.ID = pu.PID INNER JOIN
	                      CONTRACT AS c ON c.ID = PID.CONTR_ID INNER JOIN
	                          (SELECT DISTINCT pit.PRODUCT_UNIT_ID AS PU_ID, p.DATE AS P_DATE
	                            FROM          PAYMENT_IN AS p INNER JOIN
	                                                   PAYMENT_IN_ITEM AS pit ON p.ID = pit.PAYMENT_IN_ID
	                            WHERE      (@p_curreny_year = 0) OR
	                                                   (YEAR(p.DATE) = YEAR(GETDATE()))) AS pi ON pi.PU_ID = pu.ID INNER JOIN
	                      PRODUCT_COST AS pc ON pu.ID = pc.PRODUCT_UNIT_ID INNER JOIN
	                      COST_PROTOCOL AS cp ON cp.ID = pc.COST_PROTOCOL_ID
	WHERE     (pu.NSI_PRODUCT_STATE_ID <= 2) AND (c.ID = @p_contract_id) AND (cp.NSI_CURRENCY_ID = @Currency_ID) AND (@CS_End_Date IS NULL OR cs.END_DATE = @CS_End_Date) 
	                      AND (pu.NAME = @p_Prod_Unit_Name)
	GROUP BY pu.NAME, cp.NSI_CURRENCY_ID, pi.P_DATE, c.ID
    
	--Пробегаемся по курсору
	open @cursor
	-- выбираем 1-ую строку
	FETCH NEXT FROM @cursor INTO @tmpDate
	set @m_return = CONVERT(nvarchar(50),@tmpDate, 104)
	-- выбираем вторую, если полсле этого выбора @@FETCH_STATUS = 0 => всего одна строка была
	FETCH NEXT FROM @cursor INTO @tmpDate
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @m_return = @m_return + ', '

		SET @m_return = @m_return + CONVERT(nvarchar(500),@tmpDATE,104)

		FETCH NEXT FROM @cursor INTO @tmpDate

		
	END
	
	--close @cursor
	
	--set @m_return = CONVERT(nvarchar(50),@tmpDate, 104)
	--SET @m_return = @m_return + ', '

	--SET @m_return = @m_return + CONVERT(nvarchar(500),@tmpDATE,104)

    return @m_return
END